/*
*James Bienz(JB)
*This class is a SQL subclass 
*Allows / Handles for the creation / upgrade of SQLite database
*
*GNU GPL v2
*/

package com.ist412.pocke.SQL;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;

import com.ist412.pocke.DTO.ExerciseDTO;
import com.ist412.pocke.DTO.MuscleGroupDTO;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;


public class SQLMachine extends SQLiteOpenHelper {

	private static final int DATABASE_VERSION = 3;
	private static final String DATABASE_NAME = "Pocke";
	private Context context;
	
	public SQLMachine(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		this.context = context;
	}
	
	//Table creation SQL commands. To be used in onCreate()
	private static final String CREATE_TABLE_PROFILE = "CREATE TABLE Profile(" +
		"id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
		"fName TEXT NOT NULL," +
		"lName TEXT NOT NULL," +
		"age INTEGER NOT NULL," +
		"gender TEST NOT NULL," +
		"weight INTEGER NULL," +
		"hFeet REAL NULL," +
		"hInch REAL NULL," +
		"bodyArm REAL NULL," +
		"bodyChest REAL NULL," +
		"bodyThigh REAL NULL," +
		"bodyShoulder REAL NULL," +
		"bodyWaist REAL NULL," +
		"bodyCalf REAL NULL)";
	
	private static final String CREATE_TABLE_WORKOUT_TYPE = "CREATE TABLE WorkoutType(" + 
		"id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
		"typeName TEXT NOT NULL)";
	
	private static final String CREATE_TABLE_EXERCISESET = "CREATE TABLE ExerciseSet(" +
		"id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
		"EXERCISE_ID INTEGER NOT NULL," +
		"WORKOUT_ID INTEGER NOT NULL," +
		"reps INTEGER NOT NULL," +
		"weight INTEGER NULL," +
		"sequenceNum INTEGER NOT NULL," +
		"timer INTEGER NOT NULL," +
		"FOREIGN KEY (WORKOUT_ID)" +
    	"REFERENCES Workout(id)" +
    	"ON DELETE CASCADE " +
    	"ON UPDATE CASCADE," +
		"FOREIGN KEY (EXERCISE_ID)" +
		"REFERENCES Exercise (id)" +
		"ON DELETE CASCADE " +
		"ON UPDATE CASCADE)";
	
	private static final String CREATE_TABLE_WORKOUT = "CREATE TABLE Workout(" + 
		"id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
		"workoutName TEXT NOT NULL," +
		"scheduled INTEGER NOT NULL," +
		"WORKOUTTYPE_ID INTEGER NOT NULL," +
		"schedule INTEGER NOT NULL," +
		"FOREIGN KEY (WORKOUTTYPE_ID)" +
		"REFERENCES WorkoutType(id))";
	
	private static final String CREATE_TABLE_MUSCLE_GROUP = "CREATE TABLE MuscleGroup(" +
		"id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
		"name TEXT NOT NULL," +
		"muscleDirection TEXT NOT NULL)";
	
	private static final String CREATE_TABLE_EXERCISE = "CREATE TABLE Exercise(" +
		"id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
		"exerciseName TEXT NOT NULL," +
		"MUSCLEGROUP_ID INTEGER NOT NULL," +
		"FOREIGN KEY(MUSCLEGROUP_ID)" +
		"REFERENCES MuscleGroup(id))";
	
	private static final String CREATE_TABLE_WORKOUT_HAS_EXERCISE = "CREATE TABLE Workout_has_Exercise(" +
		"WORKOUT_ID INTEGER NOT NULL," + 
		"EXERCISE_ID INTEGER NOT NULL," +
		"FOREIGN KEY (WORKOUT_ID)" +
    	"REFERENCES Workout(id)" +
    	"ON DELETE CASCADE " +
    	"ON UPDATE CASCADE," +
    	"FOREIGN KEY (EXERCISE_ID)" +
    	"REFERENCES Exercise (id)" +
    	"ON DELETE NO ACTION " +
    	"ON UPDATE NO ACTION," +
    	"PRIMARY KEY(WORKOUT_ID, EXERCISE_ID))";
	
	
	private static final String CREATE_TABLE_LOG = "CREATE TABLE Log(" +
		"id INTEGER NOT NULL," +
		"date NUMERIC NOT NULL," +
		"age INTEGER NULL," +
		"weight INTEGER NULL," +
		"bmi REAL NULL," +
		"goalWeight INTEGER NULL," +
		"liftedWeight INTEGER NULL," +
 		"bodyArm REAL NULL," +
		"bodyChest REAL NULL," +
		"bodyThigh REAL NULL," + 
		"bodyShoulder REAL NULL," +
		"bodyWaist REAL NULL," +
		"bodyCalf REAL NULL," +
		"wilks REAL NULL," +
		"PRIMARY KEY (`id`))";

	
	//Methods
	
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_TABLE_PROFILE);
		db.execSQL(CREATE_TABLE_WORKOUT_TYPE);
		db.execSQL(CREATE_TABLE_WORKOUT);
		db.execSQL(CREATE_TABLE_MUSCLE_GROUP);
		db.execSQL(CREATE_TABLE_EXERCISE);
		db.execSQL(CREATE_TABLE_WORKOUT_HAS_EXERCISE);
		db.execSQL(CREATE_TABLE_EXERCISESET);
		db.execSQL(CREATE_TABLE_LOG);
		initialMuscleGroupInsert(db);
		initialExerciseInsert(db);
	}

	// Currently this function handles what is done on database upgrade. Its current
	// Functionality is to drop all the existing tables and re-create them all.
	// TODO Tackle database updates without losing data from the old.
	// TODO Write exiting data to CSV in modified tables then write back to new tables?
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {	
		db.execSQL("DROP ALL TABLES");
		onCreate(db);
	}
	
	//Reads from the DATA package muscleGroup.pocke file and inserts all the muscle groups
	//from there and adds them to the MuscleGroup table.
	private void initialMuscleGroupInsert(SQLiteDatabase db){
		File file = new File("../DATA/muscleGroups.pocke");
		try{
			BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(file)));
			String line;
			while((line = br.readLine()) != null){
				String[] lineSplit = line.split(",");
				MuscleGroupDTO muscleGroup = new MuscleGroupDTO();
				muscleGroup.setName(lineSplit[0]);
				muscleGroup.setMuscleDirection(lineSplit[1]);
				MuscleGroup.createMuscleGroup(this.context, muscleGroup);
			}
		}
		catch(Exception e){
			e.printStackTrace();
		}	
	}
	
	private void initialExerciseInsert(SQLiteDatabase db){
		File file = new File("../DATA/Exercises.pocke");
		try{
			BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(file)));
			String line;
			while((line = br.readLine()) != null){
				String[] lineSplit = line.split(",");
				ExerciseDTO exercise = new ExerciseDTO();
				exercise.setExerciseName(lineSplit[0]);
				exercise.setMuscleGroupID(Integer.parseInt(lineSplit[1]));
				Exercise.createExercise(this.context, exercise);
			}
		}
		catch(Exception e){
			e.printStackTrace();
		}	
	}	
}
